package com.sinodata.bsm.inmp.gdao;

import org.apache.log4j.Logger;

public class SqlBuilder {

    public static final Logger logger = Logger.getLogger(SqlBuilder.class);
    public static final SqlBuilder instance = new SqlBuilder();

    private SqlBuilder() {

    }

    public static SqlBuilder getInstance() {
        return instance;
    }

    /**
     * 获取主机设备的性能数据
     * <p></p>
     * @param start  
     * @param end
     * @return
     */
    public String getOracleSql(String start, String end) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select target.mtm_connection_logical_name name,");
        sb.append(" monitor.monitor_logical_name       monitorName,");
        sb.append(" measurement.msname                 measName,");
        sb.append(" rawdata.meas_value                 measvalue,");
        sb.append(" rawdata.time_stamp                 timeStamp,");
        sb.append(" rawdata.dbdate,");
        sb.append(" rawdata.start_time,");
        sb.append(" rawdata.err_msg");
        sb.append(" from (SELECT * from SM_MAP_TARGET_MON WHERE mtm_connection_logical_name like '%%') target");
        sb.append(" left join SM_DEF_MONITOR monitor");
        sb.append(" on target.monitor_id = monitor.monitor_id");
        sb.append(" left join SM_DEF_MEASUREMENT measurement");
        sb.append(" on target.monitor_id = measurement.monitor_id");
        sb.append(" left join SM_RAWDATA_MEAS_90000 rawdata");
        sb.append(" on measurement.measurement_id = rawdata.measurement_id");
        sb.append(" and target.monitor_id = rawdata.raw_monitor_id");
        sb.append(" and target.target_id = rawdata.raw_target_id");
        sb.append(" and measurement.category_id = rawdata.raw_category_id");
        sb.append("  where time_stamp >= to_date('2014-3-21', 'yyyy-MM-dd')");
        sb.append(" and  time_stamp < to_date('2014-3-21', 'yyyy-MM-dd')");
        sb.append(" order by target.mtm_connection_logical_name,");
        sb.append(" target.monitor.monitor_logical_name,");
        sb.append("  rawdata.time_stamp desc");

        logger.info(sb.toString());
        return sb.toString();
    }

    /**
     * 获取网络端口的性能数据
     * <p>采集时间，端口名称，IP，使用率，丢包率，错误包率，带宽，入带宽，出带宽</p>
     * 
     * @param start  开始时间
     * @param end    结束时间
     * @return
     */
    public String getSybaseifSql(String start, String end) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select lt_timestamp as updatetime, ");
        sb.append(" \"interface name\" as interfacename , ");
        sb.append(" \"node name\" as IP,");
        sb.append(" Utilization,");
        sb.append(" \"Discard Rate\" as discardrate, ");
        sb.append(" \"Error Rate\" as errorrate,");
        sb.append(" \"Throughput (bps)\" as throughput, ");
        sb.append(" \"Throughput in (bps)\" as throughputin, ");
        sb.append(" \"Throughput out (bps)\" as throughputout ");
        sb.append(" from f_raw_interfacemetrics ");
        sb.append(" where updatetime >= '2013-12-30' ");
        sb.append(" and updatetime < xxxx");

        logger.info(sb.toString());
        return sb.toString();
    }

    /**
     * 获取网络设备的性能数据
     * <p>设备名称，IP，空闲内存大小，内存使用率，CPU一分钟负载</p>
     * @param start
     * @param end
     * @return
     */
    public String getSybaseNwSql(String start, String end) {
        StringBuilder sb = new StringBuilder();
        sb.append(" Select  lt_timestamp updatetime,  ");
        sb.append("\"component name\" as componentname, ");
        sb.append(" \"node name\" as IP ,");
        sb.append(" \"Free Memory\" as freememory, ");
        sb.append(" \"Memory Utilization\" as memoryutilization, ");
        sb.append(" \"CPU 1min Utilization\" as cpuutilization");
        sb.append(" from f_raw_componentmetrics");
        sb.append(" where \"component type\"='CPU' or \"component type\"='MEMORY'");
        sb.append(" and lt_timestamp >= '2013-07-01'");
        sb.append(" and lt_timestamp < '2013-07-01'");

        logger.info(sb.toString());
        return sb.toString();
    }
}
